﻿Imports DataGridViewAutoFilter
Imports System.Text.RegularExpressions
Imports Excel = Microsoft.Office.Interop.Excel


Public Class Modelling_ResultsForm

    

    Private Function ProductUsed(ByVal ListOfProducts As List(Of String), ByVal Product As String) As Boolean
        Dim r As Integer
        Dim Used As Boolean
        Used = False

        If ListOfProducts.Count = 0 Then
            Used = False
        Else
            For r = 0 To ListOfProducts.Count - 1
                If ListOfProducts(r) = Product Then
                    Used = True
                End If
            Next
        End If

        ProductUsed = Used
    End Function

    Public Function StageSQL(ByVal Scenario_ID As String, ByVal Allocation_ID As String, ByVal Stage_Number As Integer, ByVal TheType As String, ByVal ShowAllFields As Boolean) As String
        Dim sql As String
        Dim TheData As DataView
        Dim i As Integer
        Dim SelectPart As String
        Dim FromPart As String
        Dim ts As String
        Dim TheAnswer As String
        Dim TheValue As String
        Dim ConditionVal As String
        Dim ConditionValAnswer As String = "0"
        Dim Learners As String
        Dim StageLearners As String
        Dim ProductLearners As String
        Dim PubProductsList As New List(Of String)
        Dim PubTablesList As New List(Of String)
        Dim ProductName As String
        Dim TheTableName As String
        Dim ProductExceptionName As String
        Dim TableExceptionName As String
        Dim ProductConditionName As String
        Dim ProductConditionNameValue As String
        Dim TableConditionName As String
        Dim TableConditionNameValue As String
        Dim SelectTable As String
        Dim TableExceptionTableName As String = ""

        Learners = ""

        ProductLearners = ""

        StageLearners = ""

        If TheType = "Stage" Then
            sql = "select * from Modelling_Steps where Scenario_ID = '" & Scenario_ID & "' " & _
              "and Allocation_ID = '" & Allocation_ID & "' and Stage_Number = " & Stage_Number & " " & _
              "order by Step_Number"

        Else
            sql = "select * from Modelling_Steps_Products where Scenario_ID = '" & Scenario_ID & "' " & _
             "and Product_Number = " & Stage_Number & " " & _
             "order by Step_Number"

        End If


        TheData = LoadDataView(sql)

        If TheData.Count = 0 Then
            'send a blank row'
            StageSQL = "Select null as RID, null as Provider_Type_Code, 0 as [Final Result], 0 as Learners "
            Exit Function
        End If

        StageSQL = ""

        If ShowAllFields = True Then
            SelectPart = "Select R.RID, R.UPIN, R.Provider_Name as [Provider Name], R.LA_Code as [Local Authority Code], R.LA_Name as [Local Authority], R.Region_Code as Region, R.Provider_Type_Description as [Provider Type], R.Provider_Subtype_Description as [Sub Type] "
        Else
            SelectPart = "Select R.RID "
        End If

        FromPart = "From All_Provider_Fields R "

        TheAnswer = ""
        TheValue = ""

        ProductName = ""
        TheTableName = ""
        ProductExceptionName = ""
        TableConditionName = ""
        TableConditionNameValue = ""
        ProductConditionName = ""
        ProductConditionNameValue = ""
        TableExceptionName = ""
        SelectTable = ""
        PubProductsList.Clear()
        PubTablesList.Clear()

        If TheData.Count <> 0 Then
            For i = 0 To TheData.Count - 1
                ProductLearners = ""
                StageLearners = ""
                'from
                ts = ""
                'main part of step

                If Trim(TheData(i).Item("Table_Name").ToString) <> "zzConstant" Then
                    If Trim(TheData(i).Item("Table_Name").ToString) <> "aaProducts" Then
                        TheTableName = "[" & TheData(i).Item("Table_Name").ToString & TheData(i).Item("DataCut_ID").ToString & "D]"
                        SelectTable = TheTableName

                        If ProductUsed(PubTablesList, TheTableName) = False Then
                            ts = "left outer join "
                            ts = ts & "(select RID, Value from " & TheData(i).Item("Table_Name").ToString & " "
                            ts = ts & "where DataCut_ID = '" & TheData(i).Item("DataCut_ID").ToString & "' "
                            ts = ts & "and Status = 'Approved') " & TheTableName & " "
                            ts = ts & "on (" & TheTableName & ".RID = R.RID) "

                            PubTablesList.Add(TheTableName)

                        End If

                    Else
                        ProductName = "[P" & Scenario_ID & TheData(i).Item("DataCut_ID").ToString & "]"
                        SelectTable = ProductName
                        If ProductUsed(PubProductsList, ProductName) = False Then

                            ts = "left outer join "
                            ts = ts & "(select RID, [Final Result] as Value, Learners from (" & _
                                      StageSQL(Scenario_ID, "", TheData(i).Item("DataCut_ID").ToString, "Product", False) & ") a" & i.ToString & " ) " & ProductName
                            ts = ts & " on (" & ProductName & ".RID = R.RID) "

                            'add it to the list
                            PubProductsList.Add(ProductName)

                        End If

                    End If

                    'exception part of step
                    If Trim(TheData(i).Item("DataCut_ID_Exception").ToString) <> "none" Then
                        If IsDBNull(TheData(i).Item("Table_Name_Exception")) Then
                            'to handle the old stages
                            TableExceptionName = SelectTable
                            TableExceptionTableName = TheData(i).Item("Table_Name").ToString

                        Else
                            TableExceptionTableName = TheData(i).Item("Table_Name_Exception").ToString
                            If TheData(i).Item("Table_Name_Exception").ToString = "aaProducts" Then
                                TableExceptionName = "[P" & Scenario_ID & TheData(i).Item("DataCut_ID_Exception").ToString & "]"
                            Else
                                TableExceptionName = "[" & TheData(i).Item("Table_Name_Exception").ToString & TheData(i).Item("DataCut_ID_Exception").ToString & "D]"
                            End If

                        End If

                        If Not TableExceptionName.StartsWith("[P") Then
                            If Not TableExceptionName.Contains("zzConstant") Then
                                If ProductUsed(PubTablesList, TableExceptionName) = False Then
                                    ts = ts & "left outer join "
                                    ts = ts & "(select RID, Value from " & Trim(TableExceptionTableName) & " "
                                    ts = ts & "where DataCut_ID = '" & TheData(i).Item("DataCut_ID_Exception").ToString & "' "
                                    ts = ts & "and Status = 'Approved') " & TableExceptionName & " "
                                    ts = ts & "on (" & TableExceptionName & ".RID = R.RID) "

                                    PubTablesList.Add(TableExceptionName)
                                End If
                            End If
                        Else
                            ProductExceptionName = "[P" & Scenario_ID & TheData(i).Item("DataCut_ID_Exception").ToString & "]"

                            If ProductUsed(PubProductsList, ProductExceptionName) = False Then
                                ts = ts & "left outer join "
                                ts = ts & "(select RID, [Final Result] as Value from (" & _
                                      StageSQL(Scenario_ID, "", TheData(i).Item("DataCut_ID_Exception").ToString, "Product", False) & ") b" & i.ToString & " ) " & ProductExceptionName
                                ts = ts & " on (" & ProductExceptionName & ".RID = R.RID) "
                                PubProductsList.Add(ProductExceptionName)
                            End If
                        End If
                    Else
                        TableExceptionName = ""
                        ProductExceptionName = ""
                    End If

                    'BRAND NEW OVER HERE...........................
                Else
                    'exception part of step
                    If Trim(TheData(i).Item("DataCut_ID_Exception").ToString) <> "none" Then
                        If IsDBNull(TheData(i).Item("Table_Name_Exception")) Then
                            'to handle the old stages
                            TableExceptionName = SelectTable
                            TableExceptionTableName = TheData(i).Item("Table_Name").ToString

                        Else
                            TableExceptionTableName = TheData(i).Item("Table_Name_Exception").ToString
                            If TheData(i).Item("Table_Name_Exception").ToString = "aaProducts" Then
                                TableExceptionName = "[P" & Scenario_ID & TheData(i).Item("DataCut_ID_Exception").ToString & "]"
                            Else
                                TableExceptionName = "[" & TheData(i).Item("Table_Name_Exception").ToString & TheData(i).Item("DataCut_ID_Exception").ToString & "D]"
                            End If

                        End If

                        If Not TableExceptionName.StartsWith("[P") Then
                            If Not TableExceptionName.Contains("zzConstant") Then
                                If ProductUsed(PubTablesList, TableExceptionName) = False Then
                                    ts = ts & "left outer join "
                                    ts = ts & "(select RID, Value from " & Trim(TableExceptionTableName) & " "
                                    ts = ts & "where DataCut_ID = '" & TheData(i).Item("DataCut_ID_Exception").ToString & "' "
                                    ts = ts & "and Status = 'Approved') " & TableExceptionName & " "
                                    ts = ts & "on (" & TableExceptionName & ".RID = R.RID) "

                                    PubTablesList.Add(TableExceptionName)
                                End If
                            End If
                        Else
                            ProductExceptionName = "[P" & Scenario_ID & TheData(i).Item("DataCut_ID_Exception").ToString & "]"

                            If ProductUsed(PubProductsList, ProductExceptionName) = False Then
                                ts = ts & "left outer join "
                                ts = ts & "(select RID, [Final Result] as Value from (" & _
                                      StageSQL(Scenario_ID, "", TheData(i).Item("DataCut_ID_Exception").ToString, "Product", False) & ") b" & i.ToString & " ) " & ProductExceptionName
                                ts = ts & " on (" & ProductExceptionName & ".RID = R.RID) "
                                PubProductsList.Add(ProductExceptionName)
                            End If
                        End If
                    Else
                        TableExceptionName = ""
                        ProductExceptionName = ""
                    End If
                    '....................
                End If

                'condition part of step
                If Trim(TheData(i).Item("Condition").ToString) <> "no" Then
                    If TheData(i).Item("Table_Name_Condition").ToString <> "aaProducts" Then

                        If TheData(i).Item("Table_Name_Condition").ToString <> "zzConstant" Then
                            TableConditionName = "[" & TheData(i).Item("Table_Name_Condition").ToString & TheData(i).Item("DataCut_ID_Condition").ToString & "D]"

                            If ProductUsed(PubTablesList, TableConditionName) = False Then
                                ts = ts & "left outer join "
                                ts = ts & "(select RID, Value from " & TheData(i).Item("Table_Name_Condition").ToString & " "
                                ts = ts & "where DataCut_ID = '" & TheData(i).Item("DataCut_ID_Condition").ToString & "' "
                                ts = ts & "and Status = 'Approved') " & TableConditionName & " "
                                ts = ts & "on (" & TableConditionName & ".RID = R.RID) "

                                PubTablesList.Add(TableConditionName)

                            End If

                        End If


                    Else

                        ProductConditionName = "[P" & Scenario_ID & TheData(i).Item("DataCut_ID_Condition").ToString & "]"

                        If ProductUsed(PubProductsList, ProductConditionName) = False Then
                            ts = ts & "left outer join "
                            ts = ts & "(select RID, [Final Result] as Value from (" & _
                                      StageSQL(Scenario_ID, "", TheData(i).Item("DataCut_ID_Condition").ToString, "Product", False) & ") c" & i.ToString & " ) " & ProductConditionName
                            ts = ts & " on (" & ProductConditionName & ".RID = R.RID) "

                            PubProductsList.Add(ProductConditionName)
                        End If



                    End If

                    If TheData(i).Item("Table_Name_Condition_Value").ToString <> "aaProducts" Then

                        If TheData(i).Item("Table_Name_Condition_Value").ToString <> "zzConstant" Then
                            TableConditionNameValue = "[" & TheData(i).Item("Table_Name_Condition_Value").ToString & TheData(i).Item("DataCut_ID_Condition_Value").ToString & "D]"

                            If ProductUsed(PubTablesList, TableConditionNameValue) = False Then
                                ts = ts & "left outer join "
                                ts = ts & "(select RID, Value from " & TheData(i).Item("Table_Name_Condition_Value").ToString & " "
                                ts = ts & "where DataCut_ID = '" & TheData(i).Item("DataCut_ID_Condition_Value").ToString & "' "
                                ts = ts & "and Status = 'Approved') " & TableConditionNameValue & " "
                                ts = ts & "on (" & TableConditionNameValue & ".RID = R.RID) "

                                PubTablesList.Add(TableConditionNameValue)

                            End If

                        End If

                    Else

                        ProductConditionNameValue = "[P" & Scenario_ID & TheData(i).Item("DataCut_ID_Condition_Value").ToString & "]"

                        If ProductUsed(PubProductsList, ProductConditionNameValue) = False Then
                            ts = ts & "left outer join "
                            ts = ts & "(select RID, [Final Result] as Value from (" & _
                                      StageSQL(Scenario_ID, "", TheData(i).Item("DataCut_ID_Condition_Value").ToString, "Product", False) & ") c" & i.ToString & " ) " & ProductConditionNameValue
                            ts = ts & " on (" & ProductConditionNameValue & ".RID = R.RID) "

                            PubProductsList.Add(ProductConditionNameValue)
                        End If


                    End If

                End If

                FromPart = FromPart & ts

                'Select
                ts = ""
                'main and exception part of step

                If Trim(TheData(i).Item("Table_Name").ToString) <> "zzConstant" Then

                    If Trim(TheData(i).Item("Table_Name").ToString) <> "aaProducts" Then
                        If ShowAllFields Then
                            ts = ", coalesce(" & TheTableName & ".Value,0) as [" & Trim(TableName(TheData(i).Item("Table_Name").ToString)) & " " & GetDatacutDescription(TheData(i).Item("Table_Name").ToString, TheData(i).Item("DataCut_ID").ToString) & " (" & i.ToString & ")] "
                        End If

                        'bothsides
                        'TheValue = "coalesce(cast(S" & i.ToString & ".Value as numeric(38,18)),0)"
                        TheValue = "coalesce(cast(" & TheTableName & ".Value as numeric(38,18)),0)"


                    Else

                        If ShowAllFields Then
                            ts = ", coalesce(" & ProductName & ".Value,0) as [" & GetProductName(Trim(TheData(i).Item("DataCut_ID")), Scenario_ID) & " (" & i.ToString & ")] "
                        End If

                        'try it here instead
                        'TheValue = "coalesce(cast(P" & i.ToString & ".Value as numeric(38,18)),0)"
                        TheValue = "coalesce(cast(" & ProductName & ".Value as numeric(38,18)),0)"
                        ProductLearners = "coalesce(cast(" & ProductName & ".Learners as numeric(38,18)),0)"


                    End If

                    'exception part of the select statement
                    If Not TableExceptionName.StartsWith("[P") Then
                        If Not TableExceptionName.Contains("zzConstant") Then
                            If Trim(TheData(i).Item("DataCut_ID_Exception").ToString) <> "none" Then
                                If ShowAllFields Then
                                    ts = ts & ", " & TableExceptionName & ".Value as [" & TableName(Trim(TableExceptionTableName)) & " " & GetDatacutDescription(TableExceptionTableName, TheData(i).Item("DataCut_ID_Exception").ToString) & " exception (" & i.ToString & ")] "
                                    'show the value of the exception
                                    ts = ts & ", case when coalesce(" & TableExceptionName & ".Value,0) = 0 then coalesce(" & SelectTable & ".Value,0) else " & TableExceptionName & ".Value end as [Value Used (" & i.ToString & ")] "
                                End If

                                TheValue = "case when coalesce(cast(" & TableExceptionName & ".Value as numeric(38,18)),0) = 0 then coalesce(cast(" & SelectTable & ".Value as numeric(38,18)),0) else coalesce(cast(" & TableExceptionName & ".Value as numeric(38,18)),0) end "

                            End If
                        Else
                            If ShowAllFields Then
                                ts = ts & ", " & TheData(i).Item("Constant_Exception").ToString & " as [Step " & i.ToString & " Constant Exception] "
                                ts = ts & ", case when coalesce(cast(" & TheData(i).Item("Constant_Exception").ToString & " as numeric(38,18)),0) = 0 then coalesce(cast(" & SelectTable & ".Value as numeric(38,18)),0) else coalesce(cast(" & TheData(i).Item("Constant_Exception").ToString & " as numeric(38,18)),0) end as [Value Used" & " (" & i.ToString & ")]"
                            End If
                            
                            TheValue = "case when coalesce(cast(" & TheData(i).Item("Constant_Exception").ToString & " as numeric(38,18)),0) = 0 then coalesce(cast(" & SelectTable & ".Value as numeric(38,18)),0) else coalesce(cast(" & TheData(i).Item("Constant_Exception").ToString & " as numeric(38,18)),0) end "
                        End If

                    Else
                        If Trim(TheData(i).Item("DataCut_ID_Exception").ToString) <> "none" Then
                            If ShowAllFields Then
                                ts = ts & ", " & ProductExceptionName & ".Value as [" & GetProductName(Trim(TheData(i).Item("DataCut_ID_Exception")), Scenario_ID) & i.ToString & " exception (" & i.ToString & ")] "
                                'show the value of the exception
                                ts = ts & ", case when coalesce(" & ProductExceptionName & ".Value,0) = 0 then coalesce(" & SelectTable & ".Value,0) else " & ProductExceptionName & ".Value end as [Value Used (" & i.ToString & ")] "
                            End If

                            TheValue = " case when coalesce(cast(" & ProductExceptionName & ".Value as numeric(38,18)),0) = 0 then coalesce(cast(" & SelectTable & ".Value as numeric(38,18)),0) else coalesce(cast(" & ProductExceptionName & ".Value as numeric(38,18)),0) end"

                        End If
                    End If

                Else
                    If ShowAllFields Then
                        ts = ", " & TheData(i).Item("Constant").ToString & " as [Step " & i.ToString & " Constant] "
                    End If

                    TheValue = "cast(" & TheData(i).Item("Constant").ToString & " as numeric(38,18))"

                    'BRAND NEW OVER HERE...............
                    'exception part of the select statement
                    If Not TableExceptionName.StartsWith("[P") Then
                        If Not TableExceptionName.Contains("zzConstant") Then
                            If Trim(TheData(i).Item("DataCut_ID_Exception").ToString) <> "none" Then

                                If ShowAllFields Then
                                    ts = ts & ", " & TableExceptionName & ".Value as [" & TableName(Trim(TableExceptionTableName)) & " " & GetDatacutDescription(TableExceptionTableName, TheData(i).Item("DataCut_ID_Exception").ToString) & " exception (" & i.ToString & ")] "
                                    'show the value of the exception
                                    ts = ts & ", case when coalesce(" & TableExceptionName & ".Value,0) = 0 then coalesce(" & TheData(i).Item("Constant").ToString & ",0) else " & TableExceptionName & ".Value end as [Value Used (" & i.ToString & ")] "
                                End If

                                TheValue = "case when coalesce(cast(" & TableExceptionName & ".Value as numeric(38,18)),0) = 0 then coalesce(cast(" & TheData(i).Item("Constant").ToString & " as numeric(38,18)),0) else coalesce(cast(" & TableExceptionName & ".Value as numeric(38,18)),0) end "

                            End If
                        Else
                            If ShowAllFields Then
                                ts = ts & ", " & TheData(i).Item("Constant_Exception").ToString & " as [Step " & i.ToString & " Constant Exception] "
                                ts = ts & ", case when coalesce(cast(" & TheData(i).Item("Constant_Exception").ToString & " as numeric(38,18)),0) = 0 then coalesce(cast(" & TheData(i).Item("Constant").ToString & " as numeric(38,18)),0) else coalesce(cast(" & TheData(i).Item("Constant_Exception").ToString & " as numeric(38,18)),0) end as [Value Used" & " (" & i.ToString & ")]"
                            End If
                            
                            TheValue = "case when coalesce(cast(" & TheData(i).Item("Constant_Exception").ToString & " as numeric(38,18)),0) = 0 then coalesce(cast(" & TheData(i).Item("Constant").ToString & " as numeric(38,18)),0) else coalesce(cast(" & TheData(i).Item("Constant_Exception").ToString & " as numeric(38,18)),0) end "
                        End If

                    Else
                        If Trim(TheData(i).Item("DataCut_ID_Exception").ToString) <> "none" Then
                            If ShowAllFields Then
                                ts = ts & ", " & ProductExceptionName & ".Value as [" & GetProductName(Trim(TheData(i).Item("DataCut_ID_Exception")), Scenario_ID) & i.ToString & " exception (" & i.ToString & ")] "
                                'show the value of the exception
                                ts = ts & ", case when coalesce(" & ProductExceptionName & ".Value,0) = 0 then coalesce(" & TheData(i).Item("Constant").ToString & ",0) else " & ProductExceptionName & ".Value end as [Value Used (" & i.ToString & ")] "
                            End If

                            TheValue = " case when coalesce(cast(" & ProductExceptionName & ".Value as numeric(38,18)),0) = 0 then coalesce(cast(" & TheData(i).Item("Constant").ToString & " as numeric(38,18)),0) else coalesce(cast(" & ProductExceptionName & ".Value as numeric(38,18)),0) end"

                        End If
                    End If


                    '..............................


                End If

                ConditionVal = ""

                'condition part of step
                If Trim(TheData(i).Item("Condition").ToString) <> "no" Then


                    If Trim(TheData(i).Item("Table_Name_Condition").ToString) <> "aaProducts" Then

                        If Trim(TheData(i).Item("Table_Name_Condition").ToString) <> "zzConstant" Then
                            If ShowAllFields Then
                                ts = ts & ", " & TableConditionName & ".Value as [" & Trim(TableName(TheData(i).Item("Table_Name_Condition").ToString)) & " " & GetDatacutDescription(TheData(i).Item("Table_Name_Condition").ToString, TheData(i).Item("DataCut_ID_Condition").ToString) & " condition (" & i.ToString & ")] "
                            End If

                            ConditionVal = "cast(" & TableConditionName & ".Value as numeric(38,18))"
                        Else
                            If ShowAllFields Then
                                ts = ts & ", " & TheData(i).Item("DataCut_ID_Condition").ToString & " as [Constant condition (" & i.ToString & ")] "
                            End If

                            ConditionVal = TheData(i).Item("DataCut_ID_Condition").ToString
                        End If



                    Else
                        If ShowAllFields Then
                            ts = ts & ", " & ProductConditionName & ".Value as [" & GetProductName(Trim(TheData(i).Item("DataCut_ID_Condition").ToString), Scenario_ID) & i.ToString & " condition (" & i.ToString & ")] "
                        End If

                        ConditionVal = "cast(" & ProductConditionName & ".Value as numeric(38,18))"



                    End If

                    If Trim(TheData(i).Item("Table_Name_Condition_Value").ToString) <> "aaProducts" Then

                        If Trim(TheData(i).Item("Table_Name_Condition_Value").ToString) <> "zzConstant" Then
                            If ShowAllFields Then
                                ts = ts & ", " & TableConditionNameValue & ".Value as [" & Trim(TableName(TheData(i).Item("Table_Name_Condition_Value").ToString)) & " " & GetDatacutDescription(TheData(i).Item("Table_Name_Condition_Value").ToString, TheData(i).Item("DataCut_ID_Condition_Value").ToString) & " condition value (" & i.ToString & ")] "
                            End If

                            ConditionValAnswer = "cast(" & TableConditionNameValue & ".Value as numeric(38,18))"
                        Else
                            If ShowAllFields Then
                                ts = ts & ", " & TheData(i).Item("DataCut_ID_Condition_Value").ToString & " as [Constant condition value (" & i.ToString & ")] "
                            End If

                            ConditionValAnswer = TheData(i).Item("DataCut_ID_Condition_Value").ToString
                        End If

                    Else
                        If ShowAllFields Then
                            ts = ts & ", " & ProductConditionNameValue & ".Value as [" & GetProductName(Trim(TheData(i).Item("DataCut_ID_Condition_Value").ToString), Scenario_ID) & i.ToString & " condition value (" & i.ToString & ")] "
                        End If

                        ConditionValAnswer = "cast(" & ProductConditionNameValue & ".Value as numeric(38,18))"


                    End If

                End If


                If TheAnswer = "" Then

                    TheAnswer = TheValue

                Else
                    'check that you are not going to devide by zero
                    If TheData(i).Item("Operator").ToString = "/" Then
                        TheAnswer = "(" & TheAnswer & " " & OperatorFix(TheData(i).Item("Operator").ToString) & " NULLIF(coalesce(" & TheValue & ",0),0))"
                    Else
                        TheAnswer = "(" & TheAnswer & " " & OperatorFix(TheData(i).Item("Operator").ToString) & " coalesce(" & TheValue & ",0))"
                    End If

                    'do the condition
                    If Trim(TheData(i).Item("Condition").ToString) <> "no" Then

                        TheAnswer = " (case when " & TheAnswer & " " & Trim(TheData(i).Item("Condition").ToString) & " " & ConditionVal & " then " & ConditionValAnswer & " else " & TheAnswer & " end) "

                    End If

                    If TheData(i).Item("Learner_Flag") = 1 Then
                        StageLearners = TheAnswer
                    End If
                End If

                If i <> 0 And i < TheData.Count - 1 Then

                    SelectPart = SelectPart & ts & ", " & TheAnswer & " as [Step " & i.ToString & " Result] "
                Else
                    SelectPart = SelectPart & ts
                End If

                'do the learners
                If ProductLearners <> "" Then
                    If Learners <> "" Then
                        Learners = Learners & " + " & ProductLearners
                    Else
                        Learners = ProductLearners
                    End If
                End If

                If StageLearners <> "" Then
                    If Learners <> "" Then
                        Learners = Learners & " + " & StageLearners
                    Else
                        Learners = StageLearners
                    End If
                End If
            Next
            If Learners <> "" Then
                Learners = ", " & Learners & " as Learners "
            Else
                Learners = ", 0 as Learners "
            End If

            'lets change it here too
            'StageSQL = SelectPart & ", coalesce(" & TheAnswer & ",0) as [Final Result] " & Learners & FromPart

            StageSQL = SelectPart & ", " & TheAnswer & " as [Final Result] " & Learners & FromPart

            'Clipboard.Clear()
            'Clipboard.SetText(StageSQL)
        End If
        TheData = Nothing
    End Function


    Public Function OperatorFix(ByVal TheOperator As String) As String

        If TheOperator = "x" Then
            OperatorFix = "*"
        Else
            OperatorFix = TheOperator
        End If
    End Function

    Public Sub HideBlankLearners()

        Dim r As Integer
        Dim c As Integer
        Dim total As Integer

        If ResultsGrid.Rows.Count = 0 Then Exit Sub

        For c = 0 To ResultsGrid.Columns.Count - 1
            If InStr(ResultsGrid.Columns(c).Name, "Learners") > 0 Then
                total = 0
                r = 0
                While r < ResultsGrid.Rows.Count And total = 0
                    If ResultsGrid.Item(c, r).Value.ToString.Length <> 0 Then
                        total = total + ResultsGrid.Item(c, r).Value
                    End If
                    r = r + 1
                End While
                If total = 0 Then
                    ResultsGrid.Columns(c).Visible = False
                End If
            End If
        Next
    End Sub

    Public Function TableName(ByVal TableID As String) As String
        Dim sql As String
        Dim TheView As DataView

        If TableID = "zzConstant" Then
            TableName = "Constant"
        Else
            If TableID = "" Then
                TableName = ""
            Else
                sql = "select * from Modelling_TableList where Table_Name = '" & TableID & "'"
                TheView = LoadDataView(sql)

                If TheView.Count = 0 Then
                    TableName = ""
                Else
                    TableName = TheView(0).Item("Table_Description").ToString
                End If
                TheView = Nothing
            End If

        End If



    End Function

    Public Function GetProductName(ByVal Product_Number As Integer, ByVal Scenario_ID As String) As String
        Dim sql As String
        Dim TheView As DataView

        sql = "select * from Modelling_Products where Scenario_ID = '" & Scenario_ID & "' and Product_Number = " & Product_Number
        TheView = LoadDataView(sql)

        GetProductName = TheView(0).Item("Product_Description").ToString
        TheView = Nothing

    End Function


    Private Sub ResultsGrid_CellFormatting(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) Handles ResultsGrid.CellFormatting
        'Dim test As Double

        'Try
        ' test = e.Value * 1
        ' Catch ex As Exception
        ' Exit Sub
        ' End Try

        If ResultsGrid.Columns(e.ColumnIndex).Name = "UPIN" Then
            e.CellStyle.Format = "#"
        Else
            If ResultsGrid.Columns(e.ColumnIndex).Name = "Total Learners" Then
                e.CellStyle.Format = "#,0"
            Else
                If ResultsGrid.Columns(e.ColumnIndex).Name.Contains("Learners") Or ResultsGrid.Columns(e.ColumnIndex).Name.Contains("Stage") Or ResultsGrid.Columns(e.ColumnIndex).Name.Contains("Allocation") Or ResultsGrid.Columns(e.ColumnIndex).Name.Contains("Budget") Then
                    e.CellStyle.Format = "#,0"
                Else
                    If ResultsGrid.Columns(e.ColumnIndex).Name.Contains("Factors") Then
                        e.CellStyle.Format = "#,0.000"
                    Else
                        If ResultsGrid.Columns(e.ColumnIndex).Name.Contains("Grand Total") Then
                            e.CellStyle.Format = "#,0"
                        Else
                            If ResultsGrid.Columns(e.ColumnIndex).Name.Contains("Final Result") Then
                                e.CellStyle.Format = "#,0.00"
                            Else
                                e.CellStyle.Format = "#,0.000000"
                            End If

                        End If
                    End If
                End If


            End If
        End If

        'adjust everything after the region column to the right
        If e.ColumnIndex > ResultsGrid.Columns("Region").Index Then
            e.CellStyle.Alignment = DataGridViewContentAlignment.MiddleRight

        Else
            e.CellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft
        End If

        'colour the results cell a nice colour

        If InStr(ResultsGrid.Columns(e.ColumnIndex).Name, "Result") > 0 Then
            e.CellStyle.BackColor = Color.Azure
        End If

        If ResultsGrid.Columns(e.ColumnIndex).Name = "Learners" Then
            e.CellStyle.BackColor = Color.LightGreen

        End If

        If ResultsGrid.Columns(e.ColumnIndex).Name = "Final Result" Then
            e.CellStyle.BackColor = Color.LightBlue
        End If

        If ResultsGrid.Columns(e.ColumnIndex).Name = "Grand Total" Then
            e.CellStyle.BackColor = Color.LightBlue
        End If

        If ResultsGrid.Columns(e.ColumnIndex).Name = "Total Learners" Then
            e.CellStyle.BackColor = Color.LightGreen

        End If

        If ResultsGrid.Columns(e.ColumnIndex).Name.Contains("Stage") Then
            e.CellStyle.BackColor = Color.LightPink
        End If

        If ResultsGrid.Columns(e.ColumnIndex).Name.Contains("ALLOCATION") Then
            e.CellStyle.BackColor = Color.LightBlue
        End If

        If ResultsGrid.Columns(e.ColumnIndex).Name.Contains("Budget") Then
            e.CellStyle.BackColor = Color.LightSalmon
        End If


    End Sub



    Private Sub ResultsGrid_BindingContextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ResultsGrid.BindingContextChanged
        Dim col As DataGridViewColumn

        For Each col In ResultsGrid.Columns
            col.HeaderCell = New DataGridViewAutoFilterColumnHeaderCell(col.HeaderCell)

        Next

        ResultsGrid.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText
        HideBlankLearners()


    End Sub

    Private Sub ExportToExcel()
        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet

        If ResultsGrid.Rows.Count = 0 Then Exit Sub

        xlApp = New Excel.Application
        xlWorkBook = xlApp.Workbooks.Add
        xlWorkSheet = xlWorkBook.Worksheets.Add

        ResultsGrid.SelectAll()

        Clipboard.SetDataObject(Me.ResultsGrid.GetClipboardContent())

        xlWorkSheet.Paste()

        xlWorkSheet.Columns("A:IV").columnwidth = 100
        xlWorkSheet.Rows.EntireRow.AutoFit()
        xlWorkSheet.Columns.EntireColumn.AutoFit()
        xlWorkSheet.Columns("A").columnwidth = 5

        xlApp.Visible = True

    End Sub




    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Me.Close()
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        ExportToExcel()

    End Sub



    Public Sub SumBy(ByVal ColumnName As String)
        If ResultsGrid.Rows.Count = 0 Then Exit Sub

        Dim r As Integer
        Dim c As Integer
        Dim sr As Integer
        Dim TS As String
        Dim Total As Decimal


        ResultsGrid.Sort(ResultsGrid.Columns(ColumnName), System.ComponentModel.ListSortDirection.Ascending)

        'setup the grid
        SummaryGrid.Rows.Clear()
        SummaryGrid.Columns.Clear()

        SummaryGrid.Columns.Add(ColumnName, ColumnName)
        For c = ResultsGrid.Columns("Region").Index + 1 To ResultsGrid.Columns.Count - 1
            SummaryGrid.Columns.Add(ResultsGrid.Columns(c).Name, ResultsGrid.Columns(c).Name)
        Next

        For c = ResultsGrid.Columns("Region").Index + 1 To ResultsGrid.Columns.Count - 1
            TS = ""
            Total = 0
            sr = 0
            For r = 0 To ResultsGrid.Rows.Count - 1
                If TS <> ResultsGrid.Item(ResultsGrid.Columns(ColumnName).Index, r).Value.ToString Then
                    'print if this is not the first row
                    If TS <> "" Then
                        If SummaryGrid.Rows.Count = sr Then
                            SummaryGrid.Rows.Add()
                        End If
                        SummaryGrid.Item(SummaryGrid.Columns(ColumnName).Index, sr).Value = TS
                        SummaryGrid.Item(SummaryGrid.Columns(ResultsGrid.Columns(c).Name).Index, sr).Value = Total
                        sr = sr + 1
                    End If
                    TS = ResultsGrid.Item(ResultsGrid.Columns(ColumnName).Index, r).Value
                    Total = 0
                End If
                If ResultsGrid.Item(c, r).Value.ToString.Length <> 0 Then
                    Total = Total + ResultsGrid.Item(c, r).Value
                End If
            Next
            'print the last summary
            If SummaryGrid.Rows.Count = sr Then
                SummaryGrid.Rows.Add()
            End If
            SummaryGrid.Item(SummaryGrid.Columns(ColumnName).Index, sr).Value = TS
            SummaryGrid.Item(SummaryGrid.Columns(ResultsGrid.Columns(c).Name).Index, sr).Value = Total
        Next

        'Now do the grand total
        SummaryGrid.Rows.Add()
        SummaryGrid.Item(0, SummaryGrid.Rows.Count - 1).Value = "Grand Total"
        For c = 1 To SummaryGrid.Columns.Count - 1
            Total = 0
            For r = 0 To SummaryGrid.Rows.Count - 2
                If SummaryGrid.Item(c, r).Value.ToString.Length <> 0 Then
                    Total = Total + SummaryGrid.Item(c, r).Value
                End If
            Next
            'print the total
            SummaryGrid.Item(c, SummaryGrid.Rows.Count - 1).Value = Total
        Next

        'Hideblank learners
        If SummaryGrid.Rows.Count = 0 Then Exit Sub

        For c = 0 To SummaryGrid.Columns.Count - 1
            If InStr(SummaryGrid.Columns(c).Name, "Learners") > 0 Then
                Total = 0
                r = 0
                While r < SummaryGrid.Rows.Count And Total = 0
                    If SummaryGrid.Item(c, r).Value.ToString.Length <> 0 Then
                        Total = Total + SummaryGrid.Item(c, r).Value
                        r = r + 1
                    End If
                End While
                If Total = 0 Then
                    SummaryGrid.Columns(c).Visible = False
                End If
            End If
        Next

    End Sub



    Private Sub ResultsGrid_MouseDown(ByVal sender As System.Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles ResultsGrid.MouseDown
        Dim hti As DataGridView.HitTestInfo = ResultsGrid.HitTest(e.X, e.Y)

        If hti.Type = DataGridViewHitTestType.ColumnHeader Then


            Dim dataBeingDragged As String = ResultsGrid.Columns(hti.ColumnIndex).Name.ToString

            ResultsGrid.DoDragDrop(dataBeingDragged, DragDropEffects.Copy)
        End If

    End Sub

    Private Sub SummaryGrid_DragEnter(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DragEventArgs) Handles SummaryGrid.DragEnter
        If e.AllowedEffect = DragDropEffects.Copy Then
            e.Effect = DragDropEffects.Copy
        End If
    End Sub

    Private Sub SummaryGrid_DragDrop(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DragEventArgs) Handles SummaryGrid.DragDrop
        SumBy(DirectCast(e.Data.GetData(GetType(String)), String))

    End Sub

    Private Sub SummaryGrid_CellFormatting(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) Handles SummaryGrid.CellFormatting
        If SummaryGrid.Columns(e.ColumnIndex).Name = "UPIN" Then
            e.CellStyle.Format = "#"
        Else
            If SummaryGrid.Columns(e.ColumnIndex).Name = "Total Learners" Then
                e.CellStyle.Format = "#,0"
            Else
                e.CellStyle.Format = "#,0.00"
            End If
        End If

        'adjust everything after the region column to the right
        If e.ColumnIndex > 0 Then
            e.CellStyle.Alignment = DataGridViewContentAlignment.MiddleRight

        Else
            e.CellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft
        End If

        'colour the Summary cell a nice colour

        If InStr(SummaryGrid.Columns(e.ColumnIndex).Name, "Result") > 0 Then
            e.CellStyle.BackColor = Color.Azure
        End If

        If SummaryGrid.Columns(e.ColumnIndex).Name = "Learners" Then
            e.CellStyle.BackColor = Color.LightGreen

        End If

        If SummaryGrid.Columns(e.ColumnIndex).Name = "Final Result" Then
            e.CellStyle.BackColor = Color.LightBlue
        End If

        If SummaryGrid.Columns(e.ColumnIndex).Name = "Grand Total" Then
            e.CellStyle.BackColor = Color.LightBlue
        End If

        If SummaryGrid.Columns(e.ColumnIndex).Name = "Total Learners" Then
            e.CellStyle.BackColor = Color.LightGreen

        End If

    End Sub

    Private Sub Modelling_ResultsForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        SummaryGrid.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText
    End Sub

    Private Function GetDatacutDescription(ByVal TableName As String, ByVal Datacut_ID As String) As String

        Dim TheData As DataView
        Dim sql As String

        sql = "Select top 1 Description from " & TableName & " where DataCut_ID = '" & Datacut_ID & "'"

        TheData = LoadDataView(sql)

        If TheData.Count = 0 Then
            GetDatacutDescription = ""
        Else
            GetDatacutDescription = TheData(0).Item("Description").ToString
        End If

    End Function

    Public Function GetAllocation(ByVal Allocation_ID As String) As String

        Dim TheData As DataView
        Dim sql As String

        sql = "Select top 1 Allocation_Description from Allocation_Lines where Allocation_ID = '" & Allocation_ID & "'"

        TheData = LoadDataView(sql)

        If TheData.Count = 0 Then
            GetAllocation = ""
        Else
            GetAllocation = TheData(0).Item("Allocation_Description").ToString
        End If

    End Function

    Private Function GetBudget(ByVal Budget_ID As String) As String

        Dim TheData As DataView
        Dim sql As String

        sql = "Select top 1 Budget_Description from Budgets where Budget_ID = '" & Budget_ID & "'"

        TheData = LoadDataView(sql)

        If TheData.Count = 0 Then
            GetBudget = ""
        Else
            GetBudget = TheData(0).Item("Budget_Description").ToString
        End If

    End Function

    Public Function ScenarioSQLv3(ByVal sql_Steps As String, ByVal TheLevel As Integer, ByVal Budget_ID As String, ByVal ShowAllFields As Boolean) As String
        Dim sql As String
        Dim TheData As DataView
        Dim i As Integer
        Dim SelectPart As String
        Dim FromPart As String
        Dim ts As String
        Dim TheAnswer As String
        Dim TheValue As String
        Dim ConditionVal As String
        Dim ConditionValAnswer As String = ""
        Dim Learners As String
        Dim StageLearners As String
        Dim ProductLearners As String

        Dim ProductsList As New List(Of String)
        Dim TablesList As New List(Of String)
        Dim ProductName As String
        Dim TheTableName As String
        Dim ProductExceptionName As String
        Dim TableExceptionName As String
        Dim ProductConditionName As String
        Dim ProductConditionNameValue As String = ""
        Dim TableConditionName As String
        Dim TableConditionNameValue As String = ""

        Dim StageAnswer As String
        Dim AllocationAnswer As String
        Dim BudgetAnswer As String
        Dim ScenarioAnswer As String

        Dim CurrentStage As String
        Dim CurrentAllocation As String
        Dim CurrentBudget As String

        Dim MainSelect As String
        Dim MainFrom As String

        Dim MainWhere As String
        Dim SelectTable As String = ""
        Dim TableExceptionTableName As String = ""
        'Final answer for if you only want one total displayed with the RID
        Dim FinalAnswer As String = ""

        Learners = ""

        ProductLearners = ""

        StageLearners = ""

        sql = sql_Steps


        TheData = LoadDataView(sql)

        If TheData.Count = 0 Then
            'send a blank row'
            ScenarioSQLv3 = "Select null as RID, null as Provider_Type_Code, null as Region, 0 as [Final Result], 0 as Learners "
            Exit Function
        End If

        ScenarioSQLv3 = ""

        If ShowAllFields Then
            MainSelect = "Select R.RID, R.UPIN, R.Provider_Name as [Provider Name], R.LA_Code as [Local Authority Code], R.LA_Name as [Local Authority], R.Region_Code as Region, R.Provider_Type_Description as [Provider Type], R.Provider_Subtype_Description as [Sub Type] "
        Else
            MainSelect = "Select R.RID "
        End If

        MainFrom = "From All_Provider_Fields R "
        MainWhere = " Where R.Lead = 1"


        If Budget_ID <> "" Then
            MainWhere = MainWhere & " and R.UPIN in (select distinct UPIN from Provider_Funding_Streams where Budget_ID = '" & Budget_ID & "')"
        End If

        'calculate what allocation lines we are working with first




        StageAnswer = ""
        AllocationAnswer = ""
        BudgetAnswer = ""
        ScenarioAnswer = ""
        CurrentAllocation = ""
        CurrentStage = ""
        CurrentBudget = ""
        ProductsList.Clear()
        TablesList.Clear()


        TheAnswer = ""
        TheValue = ""
        ProductName = ""
        TheTableName = ""
        ProductExceptionName = ""
        TableConditionName = ""
        ProductConditionName = ""
        TableExceptionName = ""
        SelectPart = ""
        FromPart = ""

        Dim NewBudget As Boolean = False
        Dim Scenario_ID As String
        Scenario_ID = ""

        Dim StepNum As Integer = -1


        If TheData.Count <> 0 Then
            Scenario_ID = TheData(i).Item("Scenario_ID").ToString
            For i = 0 To TheData.Count - 1
                StepNum = StepNum + 1
                'Every Step in a Stage
                'check if it is a new stage
                If CurrentStage <> TheData(i).Item("Stage_Number").ToString & TheData(i).Item("Allocation_ID").ToString Then
                    If CurrentStage <> "" Then
                        'prepare the select statement
                        If StageAnswer = "" Then
                            If TheAnswer <> "" Then
                                StageAnswer = "(" & TheAnswer & ")"
                            End If
                        End If

                        'prepare the allocation Total
                        If StageAnswer <> "" Then
                            If AllocationAnswer = "" Then
                                AllocationAnswer = "(" & StageAnswer & ")"
                            Else
                                AllocationAnswer = AllocationAnswer & " + (" & StageAnswer & ")"
                            End If
                        End If

                        StageAnswer = StageAnswer & " as [" & TheData(i - 1).Item("Stage_Description").ToString & " Stage]"
                        'now add it to the over all select statement
                        SelectPart = SelectPart & "," & StageAnswer

                    End If
                    'reset the variable
                    CurrentStage = TheData(i).Item("Stage_Number").ToString & TheData(i).Item("Allocation_ID").ToString
                    StageAnswer = ""
                    TheAnswer = ""
                    StepNum = 0

                End If

                'check if it is a new allocation line
                If CurrentAllocation <> TheData(i).Item("Allocation_ID").ToString Then
                    If CurrentAllocation <> "" Then

                        'prepare the budget total
                        If AllocationAnswer <> "" Then
                            If BudgetAnswer = "" Then
                                BudgetAnswer = "(" & AllocationAnswer & ")"
                            Else
                                BudgetAnswer = BudgetAnswer & " + (" & AllocationAnswer & ")"
                            End If

                            If FinalAnswer = "" Then
                                FinalAnswer = "(" & BudgetAnswer & ")"
                            Else
                                FinalAnswer = FinalAnswer & " + (" & BudgetAnswer & ")"
                            End If
                        End If

                        AllocationAnswer = AllocationAnswer & " as [ALLOCATION " & GetAllocation(CurrentAllocation) & "]"
                        'now add it to the over all select statement
                        SelectPart = SelectPart & "," & AllocationAnswer
                    End If
                    'reset the variables
                    CurrentAllocation = TheData(i).Item("Allocation_ID").ToString
                    AllocationAnswer = ""
                    StageAnswer = ""
                    TheAnswer = ""
                End If

                'check if it is new budget line
                If CurrentBudget <> TheData(i).Item("Budget_ID").ToString Then
                    If CurrentBudget <> "" Then
                        BudgetAnswer = BudgetAnswer & " as [" & GetBudget(CurrentBudget) & " Budget]"
                        'now add it to the over all select statement
                        SelectPart = SelectPart & "," & BudgetAnswer
                    End If
                    'reset the variables
                    CurrentBudget = TheData(i).Item("Budget_ID").ToString
                    BudgetAnswer = ""
                    AllocationAnswer = ""
                    StageAnswer = ""
                    TheAnswer = ""
                End If


                ProductLearners = ""
                StageLearners = ""
                'from
                ts = ""
                'main part of step

                If Trim(TheData(i).Item("Table_Name").ToString) <> "zzConstant" Then
                    If Trim(TheData(i).Item("Table_Name").ToString) <> "aaProducts" Then
                        TheTableName = "[" & TheData(i).Item("Table_Name").ToString & TheData(i).Item("DataCut_ID").ToString & "D]"
                        SelectTable = TheTableName

                        If ProductUsed(TablesList, TheTableName) = False Then
                            ts = "left outer join "
                            ts = ts & "(select RID, Value from " & TheData(i).Item("Table_Name").ToString & " "
                            ts = ts & "where DataCut_ID = '" & TheData(i).Item("DataCut_ID").ToString & "' "
                            ts = ts & "and Status = 'Approved') " & TheTableName & " "
                            ts = ts & "on (" & TheTableName & ".RID = R.RID) "

                            TablesList.Add(TheTableName)

                        End If

                    Else
                        ProductName = "[P" & Scenario_ID & TheData(i).Item("DataCut_ID").ToString & "]"
                        SelectTable = ProductName
                        If ProductUsed(ProductsList, ProductName) = False Then

                            ts = "left outer join "
                            ts = ts & "(select RID, [Final Result] as Value, Learners from (" & _
                                      StageSQL(Scenario_ID, "", TheData(i).Item("DataCut_ID").ToString, "Product", False) & ") a" & i.ToString & " ) " & ProductName
                            ts = ts & " on (" & ProductName & ".RID = R.RID) "

                            'add it to the list
                            ProductsList.Add(ProductName)

                        End If

                    End If

                    'exception part of step
                    If Trim(TheData(i).Item("DataCut_ID_Exception").ToString) <> "none" Then
                        If IsDBNull(TheData(i).Item("Table_Name_Exception")) Then
                            'to handle the old stages
                            TableExceptionName = SelectTable
                            TableExceptionTableName = TheData(i).Item("Table_Name").ToString
                        Else
                            TableExceptionTableName = TheData(i).Item("Table_Name_Exception").ToString
                            If TheData(i).Item("Table_Name_Exception").ToString = "aaProducts" Then
                                TableExceptionName = "[P" & Scenario_ID & TheData(i).Item("DataCut_ID_Exception").ToString & "]"
                            Else
                                TableExceptionName = "[" & TheData(i).Item("Table_Name_Exception").ToString & TheData(i).Item("DataCut_ID_Exception").ToString & "D]"
                            End If

                        End If

                        If Not TableExceptionName.StartsWith("[P") Then
                            If Not TableExceptionName.Contains("zzConstant") Then
                                If ProductUsed(TablesList, TableExceptionName) = False Then
                                    ts = ts & "left outer join "
                                    ts = ts & "(select RID, Value from " & Trim(TableExceptionTableName) & " "
                                    ts = ts & "where DataCut_ID = '" & TheData(i).Item("DataCut_ID_Exception").ToString & "' "
                                    ts = ts & "and Status = 'Approved') " & TableExceptionName & " "
                                    ts = ts & "on (" & TableExceptionName & ".RID = R.RID) "

                                    TablesList.Add(TableExceptionName)
                                End If
                            End If
                        Else
                            ProductExceptionName = "[P" & Scenario_ID & TheData(i).Item("DataCut_ID_Exception").ToString & "]"

                            If ProductUsed(ProductsList, ProductExceptionName) = False Then
                                ts = ts & "left outer join "
                                ts = ts & "(select RID, [Final Result] as Value from (" & _
                                      StageSQL(Scenario_ID, "", TheData(i).Item("DataCut_ID_Exception").ToString, "Product", False) & ") b" & i.ToString & " ) " & ProductExceptionName
                                ts = ts & " on (" & ProductExceptionName & ".RID = R.RID) "
                                ProductsList.Add(ProductExceptionName)
                            End If
                        End If
                    Else
                        TableExceptionName = ""
                        ProductExceptionName = ""
                    End If
                    'BRAND NEW OVER HERE...........................
                Else
                    'exception part of step
                    If Trim(TheData(i).Item("DataCut_ID_Exception").ToString) <> "none" Then
                        If IsDBNull(TheData(i).Item("Table_Name_Exception")) Then
                            'to handle the old stages
                            TableExceptionName = SelectTable
                            TableExceptionTableName = TheData(i).Item("Table_Name").ToString

                        Else
                            TableExceptionTableName = TheData(i).Item("Table_Name_Exception").ToString
                            If TheData(i).Item("Table_Name_Exception").ToString = "aaProducts" Then
                                TableExceptionName = "[P" & Scenario_ID & TheData(i).Item("DataCut_ID_Exception").ToString & "]"
                            Else
                                TableExceptionName = "[" & TheData(i).Item("Table_Name_Exception").ToString & TheData(i).Item("DataCut_ID_Exception").ToString & "D]"
                            End If

                        End If

                        If Not TableExceptionName.StartsWith("[P") Then
                            If Not TableExceptionName.Contains("zzConstant") Then
                                If ProductUsed(TablesList, TableExceptionName) = False Then
                                    ts = ts & "left outer join "
                                    ts = ts & "(select RID, Value from " & Trim(TableExceptionTableName) & " "
                                    ts = ts & "where DataCut_ID = '" & TheData(i).Item("DataCut_ID_Exception").ToString & "' "
                                    ts = ts & "and Status = 'Approved') " & TableExceptionName & " "
                                    ts = ts & "on (" & TableExceptionName & ".RID = R.RID) "

                                    TablesList.Add(TableExceptionName)
                                End If
                            End If
                        Else
                            ProductExceptionName = "[P" & Scenario_ID & TheData(i).Item("DataCut_ID_Exception").ToString & "]"

                            If ProductUsed(ProductsList, ProductExceptionName) = False Then
                                ts = ts & "left outer join "
                                ts = ts & "(select RID, [Final Result] as Value from (" & _
                                      StageSQL(Scenario_ID, "", TheData(i).Item("DataCut_ID_Exception").ToString, "Product", False) & ") b" & i.ToString & " ) " & ProductExceptionName
                                ts = ts & " on (" & ProductExceptionName & ".RID = R.RID) "
                                ProductsList.Add(ProductExceptionName)
                            End If
                        End If
                    Else
                        TableExceptionName = ""
                        ProductExceptionName = ""
                    End If
                    '....................

                End If

                'condition part of step
                If Trim(TheData(i).Item("Condition").ToString) <> "no" Then
                    If TheData(i).Item("Table_Name_Condition").ToString <> "aaProducts" Then

                        If TheData(i).Item("Table_Name_Condition").ToString <> "zzConstant" Then
                            TableConditionName = "[" & TheData(i).Item("Table_Name_Condition").ToString & TheData(i).Item("DataCut_ID_Condition").ToString & "D]"

                            If ProductUsed(TablesList, TableConditionName) = False Then
                                ts = ts & "left outer join "
                                ts = ts & "(select RID, Value from " & TheData(i).Item("Table_Name_Condition").ToString & " "
                                ts = ts & "where DataCut_ID = '" & TheData(i).Item("DataCut_ID_Condition").ToString & "' "
                                ts = ts & "and Status = 'Approved') " & TableConditionName & " "
                                ts = ts & "on (" & TableConditionName & ".RID = R.RID) "

                                TablesList.Add(TableConditionName)

                            End If

                        End If

                    Else

                        ProductConditionName = "[P" & Scenario_ID & TheData(i).Item("DataCut_ID_Condition").ToString & "]"

                        If ProductUsed(ProductsList, ProductConditionName) = False Then
                            ts = ts & "left outer join "
                            ts = ts & "(select RID, [Final Result] as Value from (" & _
                                      StageSQL(Scenario_ID, "", TheData(i).Item("DataCut_ID_Condition").ToString, "Product", False) & ") c" & i.ToString & " ) " & ProductConditionName
                            ts = ts & " on (" & ProductConditionName & ".RID = R.RID) "

                            ProductsList.Add(ProductConditionName)
                        End If

                    End If

                    'NEW NEW-----------------------the answer should be
                    If TheData(i).Item("Table_Name_Condition_Value").ToString <> "aaProducts" Then

                        If TheData(i).Item("Table_Name_Condition_Value").ToString <> "zzConstant" Then
                            TableConditionNameValue = "[" & TheData(i).Item("Table_Name_Condition_Value").ToString & TheData(i).Item("DataCut_ID_Condition_Value").ToString & "D]"

                            If ProductUsed(TablesList, TableConditionNameValue) = False Then
                                ts = ts & "left outer join "
                                ts = ts & "(select RID, Value from " & TheData(i).Item("Table_Name_Condition_Value").ToString & " "
                                ts = ts & "where DataCut_ID = '" & TheData(i).Item("DataCut_ID_Condition_Value").ToString & "' "
                                ts = ts & "and Status = 'Approved') " & TableConditionNameValue & " "
                                ts = ts & "on (" & TableConditionNameValue & ".RID = R.RID) "

                                TablesList.Add(TableConditionNameValue)

                            End If

                        End If

                    Else

                        ProductConditionNameValue = "[P" & Scenario_ID & TheData(i).Item("DataCut_ID_Condition_Value").ToString & "]"

                        If ProductUsed(ProductsList, ProductConditionNameValue) = False Then
                            ts = ts & "left outer join "
                            ts = ts & "(select RID, [Final Result] as Value from (" & _
                                      StageSQL(Scenario_ID, "", TheData(i).Item("DataCut_ID_Condition_Value").ToString, "Product", False) & ") c" & i.ToString & " ) " & ProductConditionNameValue
                            ts = ts & " on (" & ProductConditionNameValue & ".RID = R.RID) "

                            ProductsList.Add(ProductConditionNameValue)
                        End If


                    End If
                    '-------------------------------
                End If

                FromPart = FromPart & ts

                'Select
                ts = ""
                'main and exception part of step

                If Trim(TheData(i).Item("Table_Name").ToString) <> "zzConstant" Then

                    If Trim(TheData(i).Item("Table_Name").ToString) <> "aaProducts" Then
                        If ShowAllFields Then
                            ts = ", coalesce(" & TheTableName & ".Value,0) as [" & Trim(TableName(TheData(i).Item("Table_Name").ToString)) & " " & GetDatacutDescription(TheData(i).Item("Table_Name").ToString, TheData(i).Item("DataCut_ID").ToString) & " (" & i.ToString & ")] "
                        End If

                        'bothsides
                        'TheValue = "coalesce(cast(S" & i.ToString & ".Value as numeric(38,18)),0)"
                        TheValue = "coalesce(cast(" & TheTableName & ".Value as numeric(38,18)),0)"


                    Else
                        If ShowAllFields Then
                            ts = ", coalesce(" & ProductName & ".Value,0) as [" & GetProductName(Trim(TheData(i).Item("DataCut_ID")), Scenario_ID) & " (" & i.ToString & ")] "
                        End If

                        'try it here instead
                        'TheValue = "coalesce(cast(P" & i.ToString & ".Value as numeric(38,18)),0)"
                        TheValue = "coalesce(cast(" & ProductName & ".Value as numeric(38,18)),0)"
                        ProductLearners = "coalesce(cast(" & ProductName & ".Learners as numeric(38,18)),0)"


                    End If

                    'exception part of the select statement
                    If Not TableExceptionName.StartsWith("[P") Then
                        If Not TableExceptionName.Contains("zzConstant") Then
                            If Trim(TheData(i).Item("DataCut_ID_Exception").ToString) <> "none" Then
                                If ShowAllFields Then
                                    ts = ts & ", " & TableExceptionName & ".Value as [" & Trim(TableName(TableExceptionTableName)) & " " & GetDatacutDescription(TableExceptionTableName, TheData(i).Item("DataCut_ID_Exception").ToString) & " exception (" & i.ToString & ")] "
                                    'show the value of the exception
                                    ts = ts & ", case when coalesce(" & TableExceptionName & ".Value,0) = 0 then coalesce(" & SelectTable & ".Value,0) else " & TableExceptionName & ".Value end as [Value Used (" & i.ToString & ")] "
                                End If

                                TheValue = "case when coalesce(cast(" & TableExceptionName & ".Value as numeric(38,18)),0) = 0 then coalesce(cast(" & SelectTable & ".Value as numeric(38,18)),0) else coalesce(cast(" & TableExceptionName & ".Value as numeric(38,18)),0) end "

                            End If
                        Else
                            If ShowAllFields Then
                                ts = ts & ", " & TheData(i).Item("Constant_Exception").ToString & " as [Step " & i.ToString & " Constant Exception] "
                                ts = ts & ", case when coalesce(cast(" & TheData(i).Item("Constant_Exception").ToString & " as numeric(38,18)),0) = 0 then coalesce(cast(" & SelectTable & ".Value as numeric(38,18)),0) else coalesce(cast(" & TheData(i).Item("Constant_Exception").ToString & " as numeric(38,18)),0) end as [Value Used" & " (" & i.ToString & ")]"
                            End If
                            
                            TheValue = "case when coalesce(cast(" & TheData(i).Item("Constant_Exception").ToString & " as numeric(38,18)),0) = 0 then coalesce(cast(" & SelectTable & ".Value as numeric(38,18)),0) else coalesce(cast(" & TheData(i).Item("Constant_Exception").ToString & " as numeric(38,18)),0) end "
                        End If

                    Else
                        If Trim(TheData(i).Item("DataCut_ID_Exception").ToString) <> "none" Then
                            If ShowAllFields Then
                                ts = ts & ", " & ProductExceptionName & ".Value as [" & GetProductName(Trim(TheData(i).Item("DataCut_ID_Exception")), Scenario_ID) & i.ToString & " exception (" & i.ToString & ")] "
                                'show the value of the exception
                                ts = ts & ", case when coalesce(" & ProductExceptionName & ".Value,0) = 0 then coalesce(" & SelectTable & ".Value,0) else " & ProductExceptionName & ".Value end as [Value Used (" & i.ToString & ")] "
                            End If

                            TheValue = " case when coalesce(cast(" & ProductExceptionName & ".Value as numeric(38,18)),0) = 0 then coalesce(cast(" & SelectTable & ".Value as numeric(38,18)),0) else coalesce(cast(" & ProductExceptionName & ".Value as numeric(38,18)),0) end"

                        End If
                    End If

                Else
                    If ShowAllFields Then
                        ts = ", " & TheData(i).Item("Constant").ToString & " as [Step " & i.ToString & " Constant] "
                    End If

                    TheValue = "cast(" & TheData(i).Item("Constant").ToString & " as numeric(38,18))"

                    'BRAND NEW OVER HERE...............
                    'exception part of the select statement
                    If Not TableExceptionName.StartsWith("[P") Then
                        If Not TableExceptionName.Contains("zzConstant") Then
                            If Trim(TheData(i).Item("DataCut_ID_Exception").ToString) <> "none" Then
                                If ShowAllFields Then
                                    ts = ts & ", " & TableExceptionName & ".Value as [" & TableName(Trim(TableExceptionTableName)) & " " & GetDatacutDescription(TableExceptionTableName, TheData(i).Item("DataCut_ID_Exception").ToString) & " exception (" & i.ToString & ")] "
                                    'show the value of the exception
                                    ts = ts & ", case when coalesce(" & TableExceptionName & ".Value,0) = 0 then coalesce(" & TheData(i).Item("Constant").ToString & ",0) else " & TableExceptionName & ".Value end as [Value Used (" & i.ToString & ")] "
                                End If

                                TheValue = "case when coalesce(cast(" & TableExceptionName & ".Value as numeric(38,18)),0) = 0 then coalesce(cast(" & TheData(i).Item("Constant").ToString & " as numeric(38,18)),0) else coalesce(cast(" & TableExceptionName & ".Value as numeric(38,18)),0) end "

                            End If
                        Else
                            If ShowAllFields Then
                                ts = ts & ", " & TheData(i).Item("Constant_Exception").ToString & " as [Step " & i.ToString & " Constant Exception] "
                                ts = ts & ", case when coalesce(cast(" & TheData(i).Item("Constant_Exception").ToString & " as numeric(38,18)),0) = 0 then coalesce(cast(" & TheData(i).Item("Constant").ToString & " as numeric(38,18)),0) else coalesce(cast(" & TheData(i).Item("Constant_Exception").ToString & " as numeric(38,18)),0) end as [Value Used" & " (" & i.ToString & ")]"
                            End If
                            TheValue = "case when coalesce(cast(" & TheData(i).Item("Constant_Exception").ToString & " as numeric(38,18)),0) = 0 then coalesce(cast(" & TheData(i).Item("Constant").ToString & " as numeric(38,18)),0) else coalesce(cast(" & TheData(i).Item("Constant_Exception").ToString & " as numeric(38,18)),0) end "
                        End If

                    Else
                        If Trim(TheData(i).Item("DataCut_ID_Exception").ToString) <> "none" Then
                            If ShowAllFields Then
                                ts = ts & ", " & ProductExceptionName & ".Value as [" & GetProductName(Trim(TheData(i).Item("DataCut_ID_Exception")), Scenario_ID) & i.ToString & " exception (" & i.ToString & ")] "
                                'show the value of the exception
                                ts = ts & ", case when coalesce(" & ProductExceptionName & ".Value,0) = 0 then coalesce(" & TheData(i).Item("Constant").ToString & ",0) else " & ProductExceptionName & ".Value end as [Value Used (" & i.ToString & ")] "
                            End If

                            TheValue = " case when coalesce(cast(" & ProductExceptionName & ".Value as numeric(38,18)),0) = 0 then coalesce(cast(" & TheData(i).Item("Constant").ToString & " as numeric(38,18)),0) else coalesce(cast(" & ProductExceptionName & ".Value as numeric(38,18)),0) end"

                        End If
                    End If


                    '..............................

                End If

                ConditionVal = ""

                'condition part of step
                If Trim(TheData(i).Item("Condition").ToString) <> "no" Then

                    If Trim(TheData(i).Item("Table_Name_Condition").ToString) <> "aaProducts" Then

                        If Trim(TheData(i).Item("Table_Name_Condition").ToString) <> "zzConstant" Then
                            If ShowAllFields Then
                                ts = ts & ", " & TableConditionName & ".Value as [" & Trim(TableName(TheData(i).Item("Table_Name_Condition").ToString)) & " " & GetDatacutDescription(TheData(i).Item("Table_Name_Condition").ToString, TheData(i).Item("DataCut_ID_Condition").ToString) & " condition (" & i.ToString & ")] "
                            End If

                            ConditionVal = "cast(" & TableConditionName & ".Value as numeric(38,18))"
                        Else
                            If ShowAllFields Then
                                ts = ts & ", " & TheData(i).Item("DataCut_ID_Condition").ToString & " as [Constant condition (" & i.ToString & ")] "
                            End If

                            ConditionVal = TheData(i).Item("DataCut_ID_Condition").ToString
                        End If


                    Else
                        If ShowAllFields Then
                            ts = ts & ", " & ProductConditionName & ".Value as [" & GetProductName(Trim(TheData(i).Item("DataCut_ID_Condition").ToString), Scenario_ID) & i.ToString & " condition (" & i.ToString & ")] "
                        End If

                        ConditionVal = "cast(" & ProductConditionName & ".Value as numeric(38,18))"
                    End If

                    'NEW NEW NEW--------------------
                    If Trim(TheData(i).Item("Table_Name_Condition_Value").ToString) <> "aaProducts" Then

                        If Trim(TheData(i).Item("Table_Name_Condition_Value").ToString) <> "zzConstant" Then
                            If ShowAllFields Then
                                ts = ts & ", " & TableConditionNameValue & ".Value as [" & Trim(TableName(TheData(i).Item("Table_Name_Condition_Value").ToString)) & " " & GetDatacutDescription(TheData(i).Item("Table_Name_Condition_Value").ToString, TheData(i).Item("DataCut_ID_Condition_Value").ToString) & " condition value (" & i.ToString & ")] "
                            End If

                            ConditionValAnswer = "cast(" & TableConditionNameValue & ".Value as numeric(38,18))"
                        Else
                            If ShowAllFields Then
                                ts = ts & ", " & TheData(i).Item("DataCut_ID_Condition_Value").ToString & " as [Constant condition value (" & i.ToString & ")] "
                            End If

                            ConditionValAnswer = TheData(i).Item("DataCut_ID_Condition_Value").ToString
                        End If

                    Else
                        If ShowAllFields Then
                            ts = ts & ", " & ProductConditionNameValue & ".Value as [" & GetProductName(Trim(TheData(i).Item("DataCut_ID_Condition_Value").ToString), Scenario_ID) & i.ToString & " condition value (" & i.ToString & ")] "
                        End If

                        ConditionValAnswer = "cast(" & ProductConditionNameValue & ".Value as numeric(38,18))"


                    End If
                    '----------------------
                End If


                If TheAnswer = "" Then

                    TheAnswer = TheValue

                Else
                    'check that you are not going to devide by zero
                    If TheData(i).Item("Operator").ToString = "/" Then
                        TheAnswer = "(" & TheAnswer & " " & OperatorFix(TheData(i).Item("Operator").ToString) & " NULLIF(coalesce(" & TheValue & ",0),0))"
                    Else
                        TheAnswer = "(" & TheAnswer & " " & OperatorFix(TheData(i).Item("Operator").ToString) & " coalesce(" & TheValue & ",0))"
                    End If

                    'do the condition
                    If Trim(TheData(i).Item("Condition").ToString) <> "no" Then

                        'CHANGED \/ \/-------------
                        TheAnswer = " (case when " & TheAnswer & " " & Trim(TheData(i).Item("Condition").ToString) & " " & ConditionVal & " then " & ConditionValAnswer & " else " & TheAnswer & " end) "
                        '------------------
                    End If

                    If TheData(i).Item("Learner_Flag") = 1 Then
                        StageLearners = TheAnswer
                    End If
                End If

                ' If i <> 0 And i < TheData.Count - 1 Then

                ' SelectPart = SelectPart & ts & ", " & TheAnswer & " as [Step " & StepNum & " Result] "
                '   Else
                '  SelectPart = SelectPart & ts
                ' End If

                SelectPart = SelectPart & ts

                'do the learners
                If ProductLearners <> "" Then
                    If Learners <> "" Then
                        Learners = Learners & " + " & ProductLearners
                    Else
                        Learners = ProductLearners
                    End If
                End If

                If StageLearners <> "" Then
                    If Learners <> "" Then
                        Learners = Learners & " + " & StageLearners
                    Else
                        Learners = StageLearners
                    End If
                End If
            Next

            

            'update the last set of totals

            If StageAnswer = "" Then
                If TheAnswer <> "" Then
                    StageAnswer = "(" & TheAnswer & ")"

                End If
            End If

            'prepare the allocation Total
            If StageAnswer <> "" Then
                If AllocationAnswer = "" Then
                    AllocationAnswer = "(" & StageAnswer & ")"
                Else
                    AllocationAnswer = AllocationAnswer & " + (" & StageAnswer & ")"
                End If
            End If

            'prepare the budget total
            If AllocationAnswer <> "" Then
                If BudgetAnswer = "" Then
                    BudgetAnswer = "(" & AllocationAnswer & ")"
                Else
                    BudgetAnswer = BudgetAnswer & " + (" & AllocationAnswer & ")"
                End If
                FinalAnswer = FinalAnswer & " + (" & BudgetAnswer & ")"
            End If


            If StageAnswer <> "" Then
                If TheLevel <> 4 Then
                    FinalAnswer = StageAnswer
                End If
                StageAnswer = StageAnswer & " as [" & TheData(i - 1).Item("Stage_Description").ToString & " Stage]"
                SelectPart = SelectPart & "," & StageAnswer

            End If

            If TheLevel > 1 Then
                If AllocationAnswer <> "" Then

                    If TheLevel <> 4 Then
                        FinalAnswer = AllocationAnswer
                    End If

                    AllocationAnswer = AllocationAnswer & " as [ALLOCATION " & GetAllocation(CurrentAllocation) & "]"
                    SelectPart = SelectPart & "," & AllocationAnswer
                End If
            End If

            If TheLevel > 2 Then
                If BudgetAnswer <> "" Then

                    If TheLevel <> 4 Then
                        FinalAnswer = BudgetAnswer
                    End If
                    BudgetAnswer = BudgetAnswer & " as [" & GetBudget(CurrentBudget) & " Budget]"
                    SelectPart = SelectPart & "," & BudgetAnswer
                End If
            End If

            '----------

            If ShowAllFields = False Then
                SelectPart = ", " & FinalAnswer & " as [Final Result]"
            End If

            If Learners <> "" Then
                Learners = ", " & Learners & " as Learners "
            Else
                Learners = ", 0 as Learners "
            End If

            'lets change it here too
            'StageSQL = SelectPart & ", coalesce(" & TheAnswer & ",0) as [Final Result] " & Learners & FromPart

            ' ScenarioSQLv2 = SelectPart & ", " & TheAnswer & " as [Final Result] " & Learners & FromPart
            ScenarioSQLv3 = MainSelect & SelectPart & Learners & MainFrom & FromPart & MainWhere

            'Clipboard.Clear()
            'Clipboard.SetText(StageSQL)
        End If
        TheData = Nothing
    End Function

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        If ResultsGrid.RowCount = 0 Then Exit Sub
        Try
            DataGridViewAutoFilterTextBoxColumn.RemoveFilter(ResultsGrid)
        Catch ex As Exception

        End Try
    End Sub
End Class